﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Text.RegularExpressions;
using System.Data.SqlClient;


public partial class Project06 : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        //페이지 첫 로딩시 초기화
        if (!IsPostBack)
        {
            //년도생성
            SetYear(1970);
        }
    }

    /// <summary>
    /// 년도생성
    /// </summary>
    /// <param name="lower"></param>
    private void SetYear(int lower)
    {
        for (int i = DateTime.Now.Year; i >= lower; i--)
        {
            ddlYear.Items.Add(new ListItem(i.ToString() + "년", i.ToString()));
        }
    }

    /// <summary>
    /// 주어진 HTML문자열에서 필요없는것 제거
    /// </summary>
    /// <param name="src"></param>
    /// <returns></returns>
    private string MakeData(string src)
    {
        return Regex.Replace(src, "<[\\/\\!]*?[^<>]*?>", "").Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace(" ", "").Replace(",", "");
    }

    /// <summary>
    /// 실행버튼 클릭
    /// 선택된 년도를 이용하여 통계청 페이지 호출 후 실행결과 처리
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnExec_Click(object sender, EventArgs e)
    {

        WebRequestApi webRequest = new WebRequestApi(string.Format("http://index.go.kr/egams/knsoStbl/gams/showStblGams3.jsp?stts_cd=100701&idx_cd=1007&freq=Y&period={0}:{0}", ddlYear.SelectedValue));
        string strWebResult = webRequest.GetWebResponse();
        bool insertFlag = true;

        // 해당 년도 자료가 DB에 있는지 검사= 없으면 insert
        SqlConnection connection = dbConnection.getConnection(Server.MapPath("~"));
        string sqlString = "select count(*) as cnt from tblPopulation where Year=@year";
        SqlDataReader dr = null;
        try
        {
            using (connection)
            {
                using (SqlCommand command = new SqlCommand(sqlString, connection))
                {

                    command.Parameters.Add("@year", SqlDbType.Char, 4).Value = ddlYear.SelectedValue;
                    connection.Open();
                    dr = command.ExecuteReader(CommandBehavior.CloseConnection);

                    if (dr.Read())
                    {
                        if (int.Parse(dr["cnt"].ToString()) == 0) insertFlag = false;
                    }
                }

            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.ToString());
        }
        finally
        {
            if (dr != null)
                dr.Close();
            if (connection != null)
                connection.Close();
        }


        //구글 시각화 API 클래스 App_code 폴더 참조
        GoogleBizApi api = new GoogleBizApi();


        api.AddColumn("string:지역,number:인구,number:인구밀도");


        //테이블 가져오기
        Regex rx = new Regex("<table class=\"knsoTable\" border=\"0\">");
        string[] sTable = rx.Split(strWebResult);
        //Response.Write("[" + ss[1] + "]<br>");

        //테이블 내 행별(TR)로 가져오기
        rx = new Regex("</tr>");
        string [] sTr = rx.Split(sTable[1]);
        int population = 0, density = 0;
        for (int i = 2; i < sTr.Length - 1; i++)
        {
            rx = new Regex("</td>", RegexOptions.IgnoreCase);
            string[] sTd = rx.Split(sTr[i]);

            string val1 = MakeData(sTd[0]);
            string val2 = MakeData(sTd[2]);
            string val3 = MakeData(sTd[3]);

            if (val2 == "-" || val3 == "-") continue;
            // 숫자로 변환 에러시 0으로 세팅
            try { population = int.Parse(val2); }
            catch { population = 0; }

            val3 = sTd[3].Replace("<td  nowrap='nowrap' class='knsoTDD'>", "").Replace(",", "").Replace(Environment.NewLine, "").Replace("\t", "").Replace("\n", "");
            // 숫자로 변환 에러시 0으로 세팅
            try { density = int.Parse(val3); }
            catch { density=0; }

            api.SetCell(val1 + "," + population + "," + density);

            //DB에 해당 년도 자료가 없어 insert
            if (!insertFlag)
            {
                // DB Insert test
                connection = dbConnection.getConnection(Server.MapPath("~"));
                sqlString = "insert into tblPopulation(Year, Region, Population, Density) values (@year, @region, @population, @density)";
                using (connection)
                {
                    try
                    {
                        using (SqlCommand command = new SqlCommand(sqlString, connection))
                        {

                            command.Parameters.Add("@year", SqlDbType.Char, 4).Value = ddlYear.SelectedValue;
                            command.Parameters.Add("@region", SqlDbType.VarChar, 50).Value = val1;
                            command.Parameters.Add("@population", SqlDbType.BigInt, 8).Value = population;
                            command.Parameters.Add("@density", SqlDbType.BigInt, 8).Value = density;
                            connection.Open();
                            dr = command.ExecuteReader(CommandBehavior.CloseConnection);
                        }
                    }
                    catch (Exception ex)
                    {
                        Response.Write(ex.ToString() + " :: " + population + " :: " + density);
                    }
                    finally
                    {
                        if (dr != null)
                            dr.Close();
                        if (connection != null)
                            connection.Close();
                    }

                }
            }

            //Response.Write("<br>");
            //Response.Write(val1 + "," + val2 + "," + val3 + "<br>");
        }
        string strSource = api.GetSource("Table", "display_table", "showRowNumber: false");
        strSource += api.GetSource("ColumnChart", "display_chart", "width: 800, height: 300, legend: 'bottom', title: '지역별 인구/인구밀도 [단위 : 인구-천명, 인구밀도-명/㎢]'");


        //생성된 스크립트 실행
        Page.ClientScript.RegisterStartupScript(this.GetType(), "ViewData", strSource, false);



    }

}
